//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.customer;

import java.sql.*;
import java.util.Vector;
import org.jpgolay.me.persistence.OLD_RequestDB;

public class OLD_CustomerList {

    public OLD_CustomerList() {
        resultat = false;
        dbr = new OLD_RequestDB();
    }

    public boolean searchBy(int select) {
        switch (select) {
            case 1: // '\001'
                selectByCodeClient();
                break;

            case 2: // '\002'
                selectByRaisonSociale();
                break;

            case 3: // '\003'
                selectByVilles();
                break;

            case 4: // '\004'
                selectByCodePostaux();
                break;

            case 7: // '\007'
                selectByCivilite();
                break;

            case 15: // '\017'
                selectByPays();
                break;

            case 17: // '\021'
                selectByTotalEncaisser();
                break;

            case 18: // '\022'
                selectForFicheRS();
                break;

            case 19: // '\023'
                selectForFicheCC();
                break;
        }
        return resultat;
    }

    public boolean searchById(int select, String id) {
        switch (select) {
            case 5: // '\005'
                selectFamillesId(id);
                break;

            case 6: // '\006'
                selectCommercialId(id);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, String like) {
        switch (select) {
            case 8: // '\b'
                selectLikeCodeClient(like);
                break;

            case 9: // '\t'
                selectLikeRaisonSociale(like);
                break;

            case 10: // '\n'
                selectLikeVille(like);
                break;

            case 11: // '\013'
                selectLikeCodesPostaux(like);
                break;

            case 12: // '\f'
                selectLikeCivilite(like);
                break;

            case 13: // '\r'
                selectLikePays(like);
                break;

            case 14: // '\016'
                selectLikeTotalEncaisser(like);
                break;

            case 20: // '\024'
                selectForFicheLikeRS(like);
                break;

            case 21: // '\025'
                selectForFicheLikeCC(like);
                break;
        }
        return resultat;
    }

    public Vector getRangees() {
        return resultatsRangees;
    }

    public Vector getColonnes() {
        return resultatsColonnes;
    }

    private void selectByCodeClient() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by CODE_CLIENT";
        getResultat9Lines(requete);
    }

    private void selectByRaisonSociale() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by CLIENT";
        getResultat9Lines(requete);
    }

    private void selectByVilles() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by VILLE";
        getResultat9Lines(requete);
    }

    private void selectByCodePostaux() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by CP";
        getResultat9Lines(requete);
    }

    private void selectByCivilite() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by CIVILITE, CLIENT";
        getResultat9Lines(requete);
    }

    private void selectByPays() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by PAYS, CLIENT";
        getResultat9Lines(requete);
    }

    private void selectByTotalEncaisser() {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id=commerciaux.id and familles_clients_id=familles_clients.id order by ENCAISSER, CLIENT";
        getResultat9Lines(requete);
    }

    private void selectForFicheRS() {
        String requete = "select id as CODE_CLIENT, raison_sociale as RAISON_SOCIALE, telephone as TELEPHONE from clients order by RAISON_SOCIALE";
        getResultat3Lines(requete);
    }

    private void selectForFicheCC() {
        String requete = "select id as CODE_CLIENT, raison_sociale as RAISON_SOCIALE, telephone as TELEPHONE from clients order by CODE_CLIENT";
        getResultat3Lines(requete);
    }

    private void selectForFicheLikeRS(String like) {
        String requete = "select id as CODE_CLIENT, raison_sociale as RAISON_SOCIALE, telephone as TELEPHONE from clients where raison_sociale like '" + like + "%' " + "order by RAISON_SOCIALE";
        getResultat3Lines(requete);
    }

    private void selectForFicheLikeCC(String like) {
        String requete = "select id as CODE_CLIENT, raison_sociale as RAISON_SOCIALE, telephone as TELEPHONE from clients where id like '" + like + "%' " + "order by CODE_CLIENT";
        getResultat3Lines(requete);
    }

    private void selectFamillesId(String id) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where familles_clients_id='" + id + "' and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by FAMILLE";
        getResultat9Lines(requete);
    }

    private void selectCommercialId(String id) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where commerciaux_id='" + id + "' and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by FAMILLE";
        getResultat9Lines(requete);
    }

    private void selectLikeCodeClient(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where clients.id like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by CODE_CLIENT";
        getResultat9Lines(requete);
    }

    private void selectLikeRaisonSociale(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where raison_sociale like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by CODE_CLIENT";
        getResultat9Lines(requete);
    }

    private void selectLikeVille(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where ville_fact like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by VILLE";
        getResultat9Lines(requete);
    }

    private void selectLikeCodesPostaux(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where cp_fact like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by CP";
        getResultat9Lines(requete);
    }

    private void selectLikeCivilite(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where civilite like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by CIVILITE, CLIENT";
        getResultat9Lines(requete);
    }

    private void selectLikePays(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where pays_fact like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by PAYS, CLIENT";
        getResultat9Lines(requete);
    }

    private void selectLikeTotalEncaisser(String like) {
        String requete = "select clients.id as CODE_CLIENT, raison_sociale as CLIENT, ville_fact as VILLE, cp_fact as CP, contact as CONTACT, clients.telephone as TELEPHONE, clients.fax as FAX, familles_clients.id as FAMILLE, nom as COMMERCIAL from clients, commerciaux, familles_clients where total_encaisse like '" + like + "%' " + "and commerciaux_id=commerciaux.id " + "and familles_clients_id=familles_clients.id " + "order by ENCAISSER, CLIENT";
        getResultat9Lines(requete);
    }

    private void getResultat9Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                    resultatsRangees.addElement(rs.getString(4));
                    resultatsRangees.addElement(rs.getString(5));
                    resultatsRangees.addElement(rs.getString(6));
                    resultatsRangees.addElement(rs.getString(7));
                    resultatsRangees.addElement(rs.getString(8));
                    resultatsRangees.addElement(rs.getString(9));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }

    private void getResultat3Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }
    private OLD_RequestDB dbr;
    private Vector resultatsRangees;
    private Vector resultatsColonnes;
    public static final int CODE_CLIENT = 1;
    public static final int RAISON_SOCIALE = 2;
    public static final int VILLES = 3;
    public static final int CODE_POSTAUX = 4;
    public static final int CIVILITE = 7;
    public static final int PAYS = 15;
    public static final int TOTAL_ENCAISSER = 17;
    public static final int FAMILLES_ID = 5;
    public static final int COMMERCIAL_ID = 6;
    public static final int LIKE_CODE_CLIENT = 8;
    public static final int LIKE_RAISON_SOCIALE = 9;
    public static final int LIKE_VILLES = 10;
    public static final int LIKE_CODE_POSTAUX = 11;
    public static final int LIKE_CIVILITE = 12;
    public static final int LIKE_PAYS = 13;
    public static final int LIKE_TOTAL_ENCAISSER = 14;
    public static final int FICHE_RS = 18;
    public static final int FICHE_CC = 19;
    public static final int FICHE_LIKE_RS = 20;
    public static final int FICHE_LIKE_CC = 21;
    private boolean resultat;
}
